package com.ebrothers.isns.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.ebrothers.isns.common.Utils;
import com.ebrothers.isns.vo.Location;
import com.ebrothers.isns.vo.Question;
import com.ebrothers.isns.vo.User;

public class QuestionDAO extends GenericDAO {
	private UserDAO userDAO;
	private LocationDAO locationDAO;
	private AnswerDAO answerDAO;

	public QuestionDAO() {
		userDAO = new UserDAO();
		locationDAO = new LocationDAO();
		answerDAO = new AnswerDAO();
	}

	public long insertQuestion(Question q) throws DAOException {
		User owner = q.getOwner();
		if (owner.getId() == -1) {
			long userId = userDAO.getIdByUserName(owner);
			if (userId == -1) {
				userId = userDAO.insertUser(owner);
			}
			owner.setId(userId);
		}

		Location loc = q.getTriggerLocation();
		long locId = -1;
		if (loc != null) {
			locId = locationDAO.insertLocation(loc);
		}
		loc.setId(locId);

		long id = getId(q);
		if (id == -1) {
			StringBuilder sb = new StringBuilder();
			sb
					.append("insert into questions (body, type, owner, trigger_location, datetime) values ('");
			sb.append(q.getBody()).append("',");
			sb.append(q.getType()).append(",");
			sb.append(owner.getId()).append(",");
			sb.append(locId).append(",'");
			String now = Utils.dateToString(new Date());
			sb.append(now).append("');");
			String sql = sb.toString();
			insert(sql);
			id = getId(q);
		}
		return id;
	}

	private long getId(Question q) throws DAOException {
		return getIdByIdentify("questions", new String[] { "owner", "body",
				"type", "trigger_location" }, new String[] {
				String.valueOf(q.getOwner().getId()), q.getBody(),
				String.valueOf(q.getType()),
				String.valueOf(q.getTriggerLocation().getId()) });
	}

	public List<Question> getQuestionsByDate(String qDate) throws DAOException {
		List<Question> questions = new ArrayList<Question>();
		String sql = "select * from questions where datetime > '" + qDate
				+ "' order by datetime desc";
		ResultSet rs = query(sql);
		try {
			Question q;
			while (rs.next()) {
				q = new Question();
				q.setId(rs.getLong("id"));
				q.setBody(Utils.encodeText(rs.getString("body")));
				long ownerId = rs.getLong("owner");
				User owner = userDAO.getUser(ownerId);
				q.setOwner(owner);
				q.setType(rs.getInt("type"));
				q.setDatetime(Utils.dateToString(rs.getTimestamp("datetime")));
				long locId = rs.getLong("trigger_location");
				Location location = locationDAO.getLocation(locId);
				q.setTriggerLocation(location);
				int answerCount = answerDAO
						.getAnswersCountByQuestion(q.getId());
				q.setAnswerCount(answerCount);
				questions.add(q);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			closeResultSet(rs);
		}
		return questions;
	}
}
